import time
from copy import copy
import openpyxl


class Excel_xrlts:
    def __init__(self,svn_path):
        self.svn_path=svn_path
    ##读
    def xlsx_read(self,workbook_name,datalist_len=1,searce_execl=False):
        self.workbook = openpyxl.load_workbook(self.svn_path)
        sheet=self.workbook[workbook_name]
        first_column=sheet['A']
        data_list = [str(cell.value).replace('\n', '') for cell in first_column if cell.value]
        sheet._current_row = len(data_list) + datalist_len
        if searce_execl:
            first_columns = sheet['B']
            data_lists = [cell.value for cell in first_columns if cell.value]
            return data_list,data_lists,sheet
        return data_list,sheet


    def xlsx_search(self,sheet,value,xianzhi=11):
        can=sheet.max_row
        for row in sheet.iter_rows(min_row=1, max_row=can,
                                   min_col=1, max_col=2):
            for cell in row:
                if cell.value == value:
                    sheet_lists=[sheets.value for sheets in sheet[cell.row]][:xianzhi]
                    return sheet_lists,cell.row

        return None,None

    def xlsx_copy(self, xlsx_dict,data_list, sheet, respones_len=100,xlsx_len=50,searce_exec=False):
        if searce_exec:
            sheet._current_row=sheet._current_row-1
        for respones_dicts in xlsx_dict:
            sheet.append(respones_dicts)
        data_list_len = len(data_list)
        source_row = data_list_len - respones_len  # 行索引从1开始，要复制的行
        start_target_row = sheet._current_row  # 最后
        start_target_row = start_target_row - len(xlsx_dict) - xlsx_len ##从那行开始复制
        # 复制第5669行的格式到新添加的所有行
        for target_row in range(start_target_row, sheet._current_row + 1):
            for col in range(1, sheet.max_column + 1):
                source_cell = sheet.cell(row=source_row, column=col)
                target_cell = sheet.cell(row=target_row, column=col)
                # 复制单元格格式
                if source_cell.has_style:
                    target_cell._style = copy(source_cell._style)
        self.xlsx_close()
    def xlsx_close(self):
        self.workbook.save(self.svn_path)
        self.workbook.close()

    def xlsx_update(self,sheet, new_values, row_index):
        for col_index, new_value in enumerate(new_values, start=1):
            sheet.cell(row=row_index, column=col_index, value=new_value)
        self.workbook.save(self.svn_path)


    def xlsx_delete(self,sheet, row_index,delete_len=500):
        for index in range(row_index-delete_len, sheet.max_row, 1):  # 从第一行开始到最后一行逐行进行
            sheet.delete_rows(row_index)  # 则删除该行
        self.workbook.save(self.svn_path)